"""
业务数据管理API - 管理用户端(8000端口)的数据
连接用户端数据库，提供查询、统计功能
"""
from fastapi import APIRouter, HTTPException, status, Depends
from typing import Optional
from models.m import AdminUser
from utils.auth_middleware import get_current_user
from schemas.admin_schemas import ResponseModel
from tortoise import Tortoise
from datetime import datetime, timedelta


business_router = APIRouter(prefix="/admin/business", tags=["业务数据管理"])


@business_router.get("/users", response_model=ResponseModel, summary="获取用户端用户列表")
async def get_business_users(
    page: int = 1,
    page_size: int = 10,
    keyword: Optional[str] = None,
    is_verified: Optional[int] = None,
    current_user: AdminUser = Depends(get_current_user)
):
    """
    获取用户端的用户列表（从8000端口的数据库）
    """
    try:
        # 使用 'service_db' 连接查询用户端数据库
        conn = Tortoise.get_connection("service_db")
        print(f"🔍 查询参数: page={page}, page_size={page_size}, keyword={keyword}, is_verified={is_verified}")
        
        # 构建查询条件
        where_clauses = []
        params = []
        
        if keyword:
            where_clauses.append("(u.username LIKE %s OR u.email LIKE %s)")
            params.extend([f"%{keyword}%", f"%{keyword}%"])
        
        if is_verified is not None:
            if is_verified == 1:
                where_clauses.append("idc.id IS NOT NULL")
            else:
                where_clauses.append("idc.id IS NULL")
        
        where_sql = " AND ".join(where_clauses) if where_clauses else "1=1"
        
        # 查询总数
        count_sql = f"""
            SELECT COUNT(DISTINCT u.id) as total 
            FROM users u
            LEFT JOIN idcard_info idc ON u.id = idc.user_id
            WHERE {where_sql}
        """
        print(f"🔍 Count SQL: {count_sql}")
        print(f"🔍 Params: {params}")
        total_result = await conn.execute_query_dict(count_sql, params)
        total = total_result[0]['total']
        print(f"✅ Total: {total}")
        
        # 查询数据
        offset = (page - 1) * page_size
        query_sql = f"""
            SELECT u.id, u.username, u.email, u.phone, u.avatar, 
                   u.created_at, u.updated_at,
                   CASE WHEN idc.id IS NOT NULL THEN 1 ELSE 0 END as is_verified
            FROM users u
            LEFT JOIN idcard_info idc ON u.id = idc.user_id
            WHERE {where_sql}
            ORDER BY u.id DESC
            LIMIT %s OFFSET %s
        """
        params.extend([page_size, offset])
        
        print(f"🔍 Query SQL: {query_sql}")
        print(f"🔍 All Params: {params}")
        users = await conn.execute_query_dict(query_sql, params)
        print(f"✅ Users count: {len(users)}")
        
        return {
            "code": 200,
            "message": "获取成功",
            "data": {
                "total": total,
                "page": page,
                "page_size": page_size,
                "items": users
            }
        }
    
    except Exception as e:
        print(f"❌ Error in get_business_users: {e}")
        import traceback
        traceback.print_exc()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@business_router.get("/users/{user_id}", response_model=ResponseModel, summary="获取用户端用户详情")
async def get_business_user_detail(
    user_id: int,
    current_user: AdminUser = Depends(get_current_user)
):
    """
    获取用户端用户的详细信息
    """
    try:
        conn = Tortoise.get_connection("service_db")
        
        # 查询用户基本信息
        user_sql = """
            SELECT u.id, u.username, u.email, u.phone, u.avatar,
                   u.created_at, u.updated_at,
                   CASE WHEN idc.id IS NOT NULL THEN 1 ELSE 0 END as is_verified
            FROM users u
            LEFT JOIN idcard_info idc ON u.id = idc.user_id
            WHERE u.id = %s
        """
        users = await conn.execute_query_dict(user_sql, [user_id])
        
        if not users:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="用户不存在")
        
        user = users[0]
        
        # 查询实名认证信息
        idcard_sql = """
            SELECT name, idcard_number, gender, birth_date, image_url, created_at
            FROM idcard_info WHERE user_id = %s
        """
        idcard_info = await conn.execute_query_dict(idcard_sql, [user_id])
        
        # 查询训练记录数量
        record_sql = "SELECT COUNT(*) as count FROM user_training_records WHERE user_id = %s"
        record_count = await conn.execute_query_dict(record_sql, [user_id])
        
        user['idcard_info'] = idcard_info[0] if idcard_info else None
        user['training_record_count'] = record_count[0]['count']
        
        return {
            "code": 200,
            "message": "获取成功",
            "data": user
        }
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@business_router.get("/idcard-verifications", response_model=ResponseModel, summary="获取实名认证列表")
async def get_idcard_verifications(
    page: int = 1,
    page_size: int = 10,
    verify_status: Optional[str] = None,
    current_user: AdminUser = Depends(get_current_user)
):
    """
    获取实名认证列表
    """
    try:
        conn = Tortoise.get_connection("service_db")
        
        where_sql = "1=1"
        params = []
        
        # verify_status 参数已移除，因为表中没有 status 字段
        
        # 查询总数
        count_sql = f"SELECT COUNT(*) as total FROM idcard_info WHERE {where_sql}"
        total_result = await conn.execute_query_dict(count_sql, params)
        total = total_result[0]['total']
        
        # 查询数据
        offset = (page - 1) * page_size
        query_sql = f"""
            SELECT i.*, u.username
            FROM idcard_info i
            LEFT JOIN users u ON i.user_id = u.id
            WHERE {where_sql}
            ORDER BY i.id DESC
            LIMIT %s OFFSET %s
        """
        params.extend([page_size, offset])
        
        items = await conn.execute_query_dict(query_sql, params)
        
        return {
            "code": 200,
            "message": "获取成功",
            "data": {
                "total": total,
                "page": page,
                "page_size": page_size,
                "items": items
            }
        }
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@business_router.get("/training-records", response_model=ResponseModel, summary="获取训练记录列表")
async def get_training_records(
    page: int = 1,
    page_size: int = 10,
    user_id: Optional[int] = None,
    current_user: AdminUser = Depends(get_current_user)
):
    """
    获取训练记录列表
    """
    try:
        conn = Tortoise.get_connection("service_db")
        print(f"🔍 训练记录查询参数: page={page}, page_size={page_size}, user_id={user_id}")
        
        # 先查看表结构
        desc_sql = "DESCRIBE user_training_records"
        table_structure = await conn.execute_query_dict(desc_sql)
        print(f"📋 表结构: {table_structure}")
        
        where_sql = "1=1"
        params = []
        
        if user_id is not None:
            where_sql += " AND r.user_id = %s"
            params.append(user_id)
        
        # 查询总数
        count_sql = f"SELECT COUNT(*) as total FROM user_training_records r WHERE {where_sql}"
        total_result = await conn.execute_query_dict(count_sql, params)
        total = total_result[0]['total']
        print(f"✅ Total records: {total}")
        
        # 根据实际字段查询数据
        offset = (page - 1) * page_size
        query_sql = f"""
            SELECT r.*, u.username
            FROM user_training_records r
            LEFT JOIN users u ON r.user_id = u.id
            WHERE {where_sql}
            ORDER BY r.id DESC
            LIMIT %s OFFSET %s
        """
        params.extend([page_size, offset])
        
        items = await conn.execute_query_dict(query_sql, params)
        print(f"✅ Records count: {len(items)}")
        
        return {
            "code": 200,
            "message": "获取成功",
            "data": {
                "total": total,
                "page": page,
                "page_size": page_size,
                "items": items
            }
        }
    
    except Exception as e:
        print(f"❌ Error in get_training_records: {e}")
        import traceback
        traceback.print_exc()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@business_router.get("/statistics/overview", response_model=ResponseModel, summary="获取统计概览")
async def get_statistics_overview(current_user: AdminUser = Depends(get_current_user)):
    """
    获取业务数据统计概览
    """
    try:
        conn = Tortoise.get_connection("service_db")
        
        # 总用户数
        user_count_sql = "SELECT COUNT(*) as count FROM users"
        user_count = await conn.execute_query_dict(user_count_sql)
        
        # 已实名认证用户数（通过idcard_info表统计已认证的用户）
        verified_count_sql = """
            SELECT COUNT(DISTINCT user_id) as count 
            FROM idcard_info
        """
        verified_count = await conn.execute_query_dict(verified_count_sql)
        
        # 总训练记录数
        record_count_sql = "SELECT COUNT(*) as count FROM user_training_records"
        record_count = await conn.execute_query_dict(record_count_sql)
        
        # 今日新增用户
        today_sql = "SELECT COUNT(*) as count FROM users WHERE DATE(created_at) = CURDATE()"
        today_count = await conn.execute_query_dict(today_sql)
        
        # 近7天训练记录趋势
        trend_sql = """
            SELECT DATE(created_at) as date, COUNT(*) as count
            FROM user_training_records
            WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            GROUP BY DATE(created_at)
            ORDER BY date
        """
        trend_data = await conn.execute_query_dict(trend_sql)
        
        return {
            "code": 200,
            "message": "获取成功",
            "data": {
                "total_users": user_count[0]['count'],
                "verified_users": verified_count[0]['count'],
                "total_records": record_count[0]['count'],
                "today_new_users": today_count[0]['count'],
                "training_trend": trend_data
            }
        }
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )

